﻿using Smart.Security.Excel.Core;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Runtime.CompilerServices;
using System.Text;
using System.Windows.Forms;

namespace Smart.Security
{
    public class ExcelDataReader
    {
        private Encoding m_encoding;
        private Stream m_file;
        private string m_FileName;
        private XlsWorkbookGlobals m_globals;
        private XlsHeader m_hdr;
        private List<XlsWorksheet> m_sheets;
        private XlsBiffStream m_stream;
        private ushort m_version;
        private DataSet m_workbookData;

        public event BusyFinishEventHander BusyFinish;

        public event BusyStartedEventHander BusyStarted;

        public event ExcelReaderCompleteEventHander ExcelReaderComplete;

        public event ExcelReaderErrorEventHander ExcelReaderError;

        public event ExcelReaderProcessEventHander ExcelReaderProcess;

        public ExcelDataReader(Stream file)
        {
            this.m_file = null;
            this.m_hdr = null;
            this.m_stream = null;
            this.m_globals = null;
            this.m_sheets = new List<XlsWorksheet>();
            this.m_workbookData = null;
            this.m_version = 0x600;
            this.m_encoding = Encoding.Default;
            this.m_FileName = "";
        }

        public ExcelDataReader(string ExcelFile)
        {
            this.m_file = null;
            this.m_hdr = null;
            this.m_stream = null;
            this.m_globals = null;
            this.m_sheets = new List<XlsWorksheet>();
            this.m_workbookData = null;
            this.m_version = 0x600;
            this.m_encoding = Encoding.Default;
            this.m_FileName = "";
            this.m_FileName = ExcelFile;
        }

        public void ExcelRead(string FileName)
        {
            this.RaiseExcelReaderProcessEventHander(0);
            DataTable dt = new DataTable();
            this.Read(FileName);
            dt = this.m_workbookData.Tables[0];
            dt.Columns.Add("Select", typeof(bool));
            int index = 0;
            this.RaiseExcelReaderProcessEventHander(0);
            while (index < dt.Rows.Count)
            {
                if (dt.Rows[index][0] == null)
                {
                    dt.Rows.RemoveAt(index);
                }
                else if (dt.Rows[index][0].ToString() == "")
                {
                    dt.Rows.RemoveAt(index);
                }
                else
                {
                    index++;
                }
                this.RaiseExcelReaderProcessEventHander((index / dt.Rows.Count) * 100);
            }
            this.RaiseExcelReaderCompleteEventHander(dt);
        }

        public void ExcelRead(string FileName, string SheetNames)
        {
            this.RaiseBusyStartedEventHander();
            OdbcConnection connection = new OdbcConnection("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + FileName + ";");
            OdbcCommand selectCommand = new OdbcCommand("SELECT * FROM [" + SheetNames + "$]", connection);
            OdbcDataAdapter adapter = new OdbcDataAdapter(selectCommand);
            DataSet dataSet = new DataSet();
            try
            {
                connection.Open();
                adapter.Fill(dataSet, SheetNames);
                this.RaiseBusyFinishEventHander();
                DataTable dt = dataSet.Tables[0];
                this.RaiseExcelReaderCompleteEventHander(dt);
            }
            catch (Exception exception)
            {
                this.RaiseExcelReaderErrorEventHander(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void ExcelReadByADO(string FileName)
        {
            this.ExcelReadByADO(FileName, "Sheet1");
        }

        public void ExcelReadByADO(string FileName, string SheetNames)
        {
            this.RaiseBusyStartedEventHander();
            OdbcConnection connection = new OdbcConnection("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + FileName + ";");
            OdbcCommand selectCommand = new OdbcCommand("SELECT * FROM [" + SheetNames + "$]", connection);
            OdbcDataAdapter adapter = new OdbcDataAdapter(selectCommand);
            DataSet dataSet = new DataSet();
            try
            {
                connection.Open();
                adapter.Fill(dataSet, SheetNames);
                this.RaiseBusyFinishEventHander();
                DataTable dt = dataSet.Tables[0];
                DataColumn column = new DataColumn("Chọn", typeof(bool));
                dt.Columns.Add(column);
                int index = 0;
                this.RaiseExcelReaderProcessEventHander(0);
                while (index < dt.Rows.Count)
                {
                    dt.Rows[index]["Chọn"] = true;
                    if (dt.Rows[index][0] == null)
                    {
                        dt.Rows.RemoveAt(index);
                    }
                    else if (dt.Rows[index][0].ToString() == "")
                    {
                        dt.Rows.RemoveAt(index);
                    }
                    else
                    {
                        index++;
                    }
                    this.RaiseExcelReaderProcessEventHander((index / dt.Rows.Count) * 100);
                }
                dt.Columns.Add("Error", typeof(int));
                dt.Columns.Add("ErrorText", typeof(string));
                this.RaiseExcelReaderCompleteEventHander(dt);
            }
            catch (Exception exception)
            {
                this.RaiseExcelReaderErrorEventHander(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private string FormatNumber(double x)
        {
            if (Math.Floor(x) == x)
            {
                return Math.Floor(x).ToString();
            }
            return Math.Round(x, 2).ToString(CultureInfo.InvariantCulture);
        }

        public DataTable GetExcelSheetNames(string FileName)
        {
            OleDbConnection connection = null;
            DataTable oleDbSchemaTable = null;
            DataTable table2;
            try
            {
                connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
                connection.Open();
                oleDbSchemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (oleDbSchemaTable == null)
                {
                    return null;
                }
                table2 = oleDbSchemaTable;
            }
            catch (Exception)
            {
                table2 = null;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
                if (oleDbSchemaTable != null)
                {
                    oleDbSchemaTable.Dispose();
                }
            }
            return table2;
        }

        private void RaiseBusyFinishEventHander()
        {
            if (this.BusyFinish != null)
            {
                this.BusyFinish(this);
            }
        }

        private void RaiseBusyStartedEventHander()
        {
            if (this.BusyStarted != null)
            {
                this.BusyStarted(this);
            }
        }

        private void RaiseExcelReaderCompleteEventHander(DataTable dt)
        {
            if (this.ExcelReaderComplete != null)
            {
                this.ExcelReaderComplete(this, dt);
            }
        }

        private void RaiseExcelReaderErrorEventHander(string Message)
        {
            if (this.ExcelReaderError != null)
            {
                this.ExcelReaderError(this, Message);
            }
        }

        private void RaiseExcelReaderProcessEventHander(int Position)
        {
            if (this.ExcelReaderProcess != null)
            {
                this.ExcelReaderProcess(this, Position);
            }
        }

        public ExcelDataReader Read()
        {
            ExcelDataReader reader = null;
            try
            {
                FileStream file = new FileStream(this.m_FileName, FileMode.Open, FileAccess.Read, FileShare.Read);
                reader = new ExcelDataReader(file);
                file.Close();
            }
            catch (Exception exception)
            {
                MessageBox.Show("Failed to read file: \n" + exception.Message);
                return null;
            }
            return reader;
        }

        public void Read(string FileName)
        {
            FileStream stream = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read);
            this.m_file = stream;
            this.RaiseExcelReaderProcessEventHander(0);
            this.m_hdr = XlsHeader.ReadHeader(this.m_file);
            this.RaiseExcelReaderProcessEventHander(10);
            XlsRootDirectory directory = new XlsRootDirectory(this.m_hdr);
            this.RaiseExcelReaderProcessEventHander(20);
            XlsDirectoryEntry entry = directory.FindEntry("Workbook");
            this.RaiseExcelReaderProcessEventHander(30);
            if (entry == null)
            {
                entry = directory.FindEntry("Book");
            }
            this.RaiseExcelReaderProcessEventHander(40);
            if (entry == null)
            {
                throw new FileNotFoundException("Oops! Neither stream 'Workbook' nor 'Book' was found in file");
            }
            this.RaiseExcelReaderProcessEventHander(60);
            if (entry.EntryType != STGTY.STGTY_STREAM)
            {
                throw new FormatException("Oops! Workbook directory entry is not a Stream");
            }
            this.RaiseExcelReaderProcessEventHander(80);
            this.m_stream = new XlsBiffStream(this.m_hdr, entry.StreamFirstSector);
            this.RaiseExcelReaderProcessEventHander(100);
            this.ReadWorkbookGlobals();
            GC.Collect();
            this.RaiseBusyStartedEventHander();
            this.m_workbookData = new DataSet();
            for (int i = 0; i < this.m_sheets.Count; i++)
            {
                if (this.ReadWorksheet(this.m_sheets[i]))
                {
                    this.m_workbookData.Tables.Add(this.m_sheets[i].Data);
                }
            }
            this.RaiseBusyFinishEventHander();
            this.m_globals.SST = null;
            this.m_globals = null;
            this.m_sheets = null;
            this.m_stream = null;
            this.m_hdr = null;
            GC.Collect();
        }

        public static ExcelDataReader Read(string FileName, ComboBox cboSheet)
        {
            ExcelDataReader reader = null;
            try
            {
                FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read);
                reader = new ExcelDataReader(file);
                file.Close();
                cboSheet.Items.Clear();
                cboSheet.DisplayMember = "TableName";
                foreach (DataTable table in reader.WorkbookData.Tables)
                {
                    cboSheet.Items.Add(table);
                }
                if (cboSheet.Items.Count == 0)
                {
                    return null;
                }
                cboSheet.SelectedIndex = 0;
            }
            catch (Exception exception)
            {
                MessageBox.Show("Failed to read file: \n" + exception.Message);
                return null;
            }
            return reader;
        }

        private void ReadWorkbookGlobals()
        {
            XlsBiffRecord record;
            this.m_globals = new XlsWorkbookGlobals();
            this.m_stream.Seek(0, SeekOrigin.Begin);
            XlsBiffBOF fbof = this.m_stream.Read() as XlsBiffBOF;
            if ((fbof == null) || (fbof.Type != BIFFTYPE.WorkbookGlobals))
            {
                throw new InvalidDataException("Oops! Stream has invalid data");
            }
            this.m_version = fbof.Version;
            this.m_encoding = Encoding.Unicode;
            bool flag = this.m_version >= 0x600;
            bool flag2 = false;
            this.RaiseExcelReaderProcessEventHander(0);
            while ((record = this.m_stream.Read()) != null)
            {
                this.RaiseExcelReaderProcessEventHander(this.m_stream.Position / this.m_stream.Size);
                switch (record.ID)
                {
                    case BIFFRECORDTYPE.EOF:
                        if (this.m_globals.SST != null)
                        {
                            this.m_globals.SST.ReadStrings();
                        }
                        return;

                    case BIFFRECORDTYPE.FORMAT_V23:
                    case BIFFRECORDTYPE.FORMAT:
                        this.m_globals.Formats.Add(record);
                        break;

                    case BIFFRECORDTYPE.FONT:
                    case BIFFRECORDTYPE.FONT_V34:
                        this.m_globals.Fonts.Add(record);
                        break;

                    case BIFFRECORDTYPE.CODEPAGE:
                        this.m_globals.CodePage = (XlsBiffSimpleValueRecord)record;
                        this.m_encoding = Encoding.GetEncoding(this.m_globals.CodePage.Value);
                        break;

                    case BIFFRECORDTYPE.XF_V2:
                    case BIFFRECORDTYPE.XF:
                    case BIFFRECORDTYPE.XF_V3:
                    case BIFFRECORDTYPE.XF_V4:
                        this.m_globals.ExtendedFormats.Add(record);
                        break;

                    case BIFFRECORDTYPE.CONTINUE:
                        if (flag2)
                        {
                            XlsBiffContinue fragment = (XlsBiffContinue)record;
                            this.m_globals.SST.Append(fragment);
                        }
                        break;

                    case BIFFRECORDTYPE.BOUNDSHEET:
                        {
                            XlsBiffBoundSheet refSheet = (XlsBiffBoundSheet)record;
                            if (refSheet.Type == XlsBiffBoundSheet.SheetType.Worksheet)
                            {
                                refSheet.IsV8 = flag;
                                refSheet.UseEncoding = this.m_encoding;
                                this.m_sheets.Add(new XlsWorksheet(this.m_globals.Sheets.Count, refSheet));
                                this.m_globals.Sheets.Add(refSheet);
                            }
                            break;
                        }
                    case BIFFRECORDTYPE.COUNTRY:
                        this.m_globals.Country = record;
                        break;

                    case BIFFRECORDTYPE.INTERFACEHDR:
                        this.m_globals.InterfaceHdr = (XlsBiffInterfaceHdr)record;
                        break;

                    case BIFFRECORDTYPE.MMS:
                        this.m_globals.MMS = record;
                        break;

                    case BIFFRECORDTYPE.SST:
                        this.m_globals.SST = (XlsBiffSST)record;
                        flag2 = true;
                        break;

                    case BIFFRECORDTYPE.EXTSST:
                        this.m_globals.ExtSST = record;
                        flag2 = false;
                        break;

                    default:
                        {
                            continue;
                        }
                }
            }
        }

        private bool ReadWorksheet(XlsWorksheet sheet)
        {
            XlsBiffRecord record;
            int num2;
            this.m_stream.Seek((int)sheet.DataOffset, SeekOrigin.Begin);
            XlsBiffBOF fbof = this.m_stream.Read() as XlsBiffBOF;
            if ((fbof == null) || (fbof.Type != BIFFTYPE.Worksheet))
            {
                return false;
            }
            XlsBiffIndex index = this.m_stream.Read() as XlsBiffIndex;
            bool flag = this.m_version >= 0x600;
            if (index == null)
            {
                return false;
            }
            index.IsV8 = flag;
            DataTable table = new DataTable(sheet.Name);
            XlsBiffDimensions dimensions = null;
            do
            {
                record = this.m_stream.Read();
                if (record.ID == BIFFRECORDTYPE.DIMENSIONS)
                {
                    dimensions = (XlsBiffDimensions)record;
                    break;
                }
            }
            while (record.ID != BIFFRECORDTYPE.ROW);
            int lastColumn = 0x100;
            if (dimensions != null)
            {
                dimensions.IsV8 = flag;
                lastColumn = dimensions.LastColumn;
                sheet.Dimensions = dimensions;
            }
            for (num2 = 0; num2 < lastColumn; num2++)
            {
                int num6 = num2 + 1;
                table.Columns.Add("Column" + num6.ToString(), typeof(string));
            }
            sheet.Data = table;
            uint lastExistingRow = index.LastExistingRow;
            if (index.LastExistingRow > index.FirstExistingRow)
            {
                table.BeginLoadData();
                for (num2 = 0; num2 <= lastExistingRow; num2++)
                {
                    table.Rows.Add(table.NewRow());
                }
                uint[] dbCellAddresses = index.DbCellAddresses;
                for (num2 = 0; num2 < dbCellAddresses.Length; num2++)
                {
                    XlsBiffRecord record2;
                    string str;
                    ushort columnIndex;
                    bool flag3;
                    XlsBiffDbCell cell = (XlsBiffDbCell)this.m_stream.ReadAt((int)dbCellAddresses[num2]);
                    XlsBiffRow row = null;
                    int rowAddress = cell.RowAddress;
                    do
                    {
                        row = this.m_stream.ReadAt(rowAddress) as XlsBiffRow;
                        if (row == null)
                        {
                            break;
                        }
                        rowAddress += row.Size;
                    }
                    while (row != null);
                    goto Label_059C;
                Label_0222:
                    record2 = this.m_stream.ReadAt(rowAddress);
                    rowAddress += record2.Size;
                    if ((record2 is XlsBiffDbCell) || (record2 is XlsBiffEOF))
                    {
                        continue;
                    }
                    XlsBiffBlankCell cell2 = record2 as XlsBiffBlankCell;
                    if (((cell2 != null) && (cell2.ColumnIndex < lastColumn)) && (cell2.RowIndex <= lastExistingRow))
                    {
                        switch (cell2.ID)
                        {
                            case BIFFRECORDTYPE.BLANK_OLD:
                            case BIFFRECORDTYPE.BOOLERR_OLD:
                            case BIFFRECORDTYPE.MULBLANK:
                            case BIFFRECORDTYPE.BLANK:
                                goto Label_059C;

                            case BIFFRECORDTYPE.INTEGER_OLD:
                            case BIFFRECORDTYPE.INTEGER:
                                goto Label_0367;

                            case BIFFRECORDTYPE.NUMBER_OLD:
                            case BIFFRECORDTYPE.NUMBER:
                                goto Label_03A0;

                            case BIFFRECORDTYPE.LABEL_OLD:
                            case BIFFRECORDTYPE.RSTRING:
                            case BIFFRECORDTYPE.LABEL:
                                goto Label_03D6;

                            case BIFFRECORDTYPE.FORMULA_OLD:
                            case BIFFRECORDTYPE.FORMULA:
                                goto Label_04E5;

                            case BIFFRECORDTYPE.MULRK:
                                columnIndex = cell2.ColumnIndex;
                                goto Label_04C2;

                            case BIFFRECORDTYPE.RK:
                                table.Rows[cell2.RowIndex][cell2.ColumnIndex] = this.FormatNumber(((XlsBiffRKCell)cell2).Value);
                                goto Label_059C;

                            case BIFFRECORDTYPE.LABELSST:
                                goto Label_0406;
                        }
                    }
                    goto Label_059C;
                Label_0367:
                    table.Rows[cell2.RowIndex][cell2.ColumnIndex] = ((XlsBiffIntegerCell)cell2).Value.ToString();
                    goto Label_059C;
                Label_03A0:
                    table.Rows[cell2.RowIndex][cell2.ColumnIndex] = this.FormatNumber(((XlsBiffNumberCell)cell2).Value);
                    goto Label_059C;
                Label_03D6:
                    table.Rows[cell2.RowIndex][cell2.ColumnIndex] = ((XlsBiffLabelCell)cell2).Value;
                    goto Label_059C;
                Label_0406:
                    str = this.m_globals.SST.GetString(((XlsBiffLabelSSTCell)cell2).SSTIndex);
                    table.Rows[cell2.RowIndex][cell2.ColumnIndex] = str;
                    goto Label_059C;
                Label_048D:
                    table.Rows[cell2.RowIndex][columnIndex] = this.FormatNumber(((XlsBiffMulRKCell)cell2).GetValue(columnIndex));
                    columnIndex = (ushort)(columnIndex + 1);
                Label_04C2:
                    if (columnIndex <= ((XlsBiffMulRKCell)cell2).LastColumnIndex)
                    {
                        goto Label_048D;
                    }
                    goto Label_059C;
                Label_04E5:
                    ((XlsBiffFormulaCell)cell2).UseEncoding = this.m_encoding;
                    object obj2 = ((XlsBiffFormulaCell)cell2).Value;
                    if (obj2 == null)
                    {
                        obj2 = string.Empty;
                    }
                    else if (obj2 is FORMULAERROR)
                    {
                        obj2 = "#" + ((FORMULAERROR)obj2).ToString();
                    }
                    else if (obj2 is double)
                    {
                        obj2 = this.FormatNumber((double)obj2);
                    }
                    table.Rows[cell2.RowIndex][cell2.ColumnIndex] = obj2.ToString();
                Label_059C:
                    flag3 = true;
                    goto Label_0222;
                }
                table.EndLoadData();
            }
            return true;
        }

        public string FileName
        {
            get
            {
                return this.m_FileName;
            }
            set
            {
                this.m_FileName = value;
            }
        }

        public DataSet WorkbookData
        {
            get
            {
                return this.m_workbookData;
            }
        }

        public delegate void BusyFinishEventHander(object sender);

        public delegate void BusyStartedEventHander(object sender);

        public delegate void ExcelReaderCompleteEventHander(object sender, DataTable dt);

        public delegate void ExcelReaderErrorEventHander(object sender, string Message);

        public delegate void ExcelReaderProcessEventHander(object sender, int Position);
    }
}
